package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.dao.IRoomDao;
import com.eling.elcms.basedoc.dao.IRoomTypeDao;
import com.eling.elcms.basedoc.model.RoomType;
import com.eling.elcms.basedoc.model.view.RoomPanoramaView;
import com.eling.elcms.basedoc.model.view.RoomStatisticsView;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.IShiftChangeDao;
import com.eling.elcms.travel.model.ShiftChange;
import com.eling.elcms.travel.model.view.ChargeTotalView;
import com.eling.elcms.travel.model.view.CheckInCountView;
import com.eling.elcms.travel.model.view.MemberCountView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Repository
public class ShiftChangeDaoHibernate extends GenericDaoHibernate<ShiftChange, Long>implements IShiftChangeDao {

	public ShiftChangeDaoHibernate() {
		super(ShiftChange.class);
	}

	@Autowired
	private IRoomTypeDao roomTypeDao;

	@Autowired
	private IRoomDao roomDao;

	@SuppressWarnings("unchecked")
	@Override
	public List<CheckInCountView> queryCheckInCount(Date shiftChangeDate) {

		StringBuffer sql = new StringBuffer();
		Date currentTime = new Date();

		Calendar cal1 = Calendar.getInstance();
		cal1.setTime(shiftChangeDate);

		Calendar cal2 = Calendar.getInstance();
		cal2.setTime(currentTime);

		RoomType roomType = new RoomType();
		List<RoomType> roomTypeList = roomTypeDao.query(roomType);

		List<CheckInCountView> viewList = new ArrayList<>();

		// 如果日期是今天
		if (cal1.get(Calendar.YEAR) == cal2.get(Calendar.YEAR) && cal1.get(Calendar.MONTH) == cal2.get(Calendar.MONTH)
				&& cal1.get(Calendar.DAY_OF_MONTH) == cal2.get(Calendar.DAY_OF_MONTH)) {
			sql.append(" SELECT count(br.pkRoom),trt.`name`,trt.pkRoomType FROM `bd_room` br   ");
			sql.append(" LEFT JOIN travel_roomtype trt                                         ");
			sql.append(" ON br.pkRoomType=trt.pkRoomType                                       ");
			sql.append(" WHERE br.`status` = 'CheckIn'                                         ");
			sql.append(" GROUP BY trt.pkRoomType;                                              ");

			List<Object[]> objList = getSession().createNativeQuery(sql.toString()).list();

			int count = 0;
			CheckInCountView cicView = new CheckInCountView();

			for (int i = 0; i < objList.size(); i++) {
				Object[] objects = objList.get(i);
				count += objects[0] == null ? 0 : Integer.parseInt(objects[0].toString());
			}
			cicView.setRoomCount(count);
			cicView.setRoomTypeName("总数");
			if (roomTypeList != null && !roomTypeList.isEmpty()) {
				for (int i = 0; i < roomTypeList.size(); i++) {
					CheckInCountView checkInCountView = new CheckInCountView();
					for (int j = 0; j < objList.size(); j++) {
						Object[] objects = objList.get(j);
						if (roomTypeList.get(i).getPkRoomType() == Integer.parseInt(objects[2].toString())) {
							checkInCountView.setRoomCount(Integer.parseInt(objects[0].toString()));
							checkInCountView.setRoomTypeName(objects[1].toString());
							viewList.add(checkInCountView);
							break;
						}
					}
					if (i == roomTypeList.size()) {
						checkInCountView.setRoomCount(0);
						checkInCountView.setRoomTypeName(roomTypeList.get(i).getName());
						viewList.add(checkInCountView);
					}
				}
			}
		}
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RoomStatisticsView> queryStatistics(Date startDate, Date endDate) {
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT			   ");
		sql.append("  	r.`status`,		   ");
		sql.append("  	COUNT(r.`status`) count	   ");
		sql.append("  FROM			   ");
		sql.append("  	bd_room r		   ");
		sql.append("	JOIN travel_roomtype trt ON trt.pkRoomType = r.pkRoomType  ");
		sql.append("  WHERE 1=1 ");
		sql.append("  GROUP BY			   ");
		sql.append("  	r.`status`		   ");
		RoomStatisticsView roomStatisticsView = new RoomStatisticsView();

		List<Object[]> objectList = getSession().createNativeQuery(sql.toString()).list();

		roomStatisticsView.setAppoint(0);
		roomStatisticsView.setCheckIn(0);
		roomStatisticsView.setClear(0);
		roomStatisticsView.setDirty(0);
		roomStatisticsView.setFree(0);
		roomStatisticsView.setRepairing(0);
		roomStatisticsView.setSelfUse(0);
		roomStatisticsView.setTemporary(0);
		roomStatisticsView.setWillGo(0);

		for (Object[] obj : objectList) {
			if(obj[0]==null){
				continue;
			}
			String status = obj[0].toString();
			Integer count = obj[1].toString() == null ? 0 : new Integer(obj[1].toString());
			switch (status) {
			case "Clear":
				roomStatisticsView.setClear(count);
				break;
			case "Dirty":
				roomStatisticsView.setDirty(count);
				break;
			case "CheckIn":
				roomStatisticsView.setCheckIn(count);
				break;
			case "WillGo":
				roomStatisticsView.setWillGo(count);
				break;
			case "Appoint":
				roomStatisticsView.setAppoint(count);
				break;
			case "Repairing":
				roomStatisticsView.setRepairing(count);
				break;
			case "Temporary":
				roomStatisticsView.setTemporary(count);
				break;
			case "SelfUse":
				roomStatisticsView.setSelfUse(count);
				break;
			case "Free":
				roomStatisticsView.setFree(count);
				break;
			}
		}

		Long orgBuilding = null;
		Long roomType = null;
		List<RoomPanoramaView> rplist = roomDao.queryWilGo(startDate, orgBuilding, null, roomType);
		roomStatisticsView.setWillGo(rplist.size());
		List<RoomPanoramaView> appointList = roomDao.queryAppoint(startDate, endDate, orgBuilding, null, roomType);
		roomStatisticsView.setAppoint(appointList.size());

		List<RoomStatisticsView> list = new ArrayList<RoomStatisticsView>();
		list.add(roomStatisticsView);
		return list;

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MemberCountView> queryMember(Date shiftChangeDate) {

		MemberCountView memberCountView = new MemberCountView();

		StringBuffer sql = new StringBuffer();
		// 老人团男女数
		sql.append(" SELECT bp.sex,count(*) FROM travel_checkinimplement tci                          ");
		sql.append(" JOIN travel_groupreserve tgr ON tgr.pkGroupReserve = tci.pkGroupReserve          ");
		sql.append(" JOIN travel_roompersonalInfo trp ON trp.pkCIImplement = tci.pkCIImplement        ");
		sql.append(" JOIN bd_personalinfo bp ON trp.pkPersonalInfo = bp.pkPersonalInfo                ");
		sql.append(" WHERE tgr.checkInDate <= :shiftChangeDate AND tgr.checkOutDate >= :shiftChangeDate			  ");
		sql.append("  GROUP BY bp.sex                                                                 ");
		List<Object[]> gList = getSession().createNativeQuery(sql.toString()).setParameter("shiftChangeDate", shiftChangeDate).list();
		// 散客男女数
		StringBuffer sql2 = new StringBuffer();
		sql2.append(
				" SELECT bp.sex,count(*) FROM travel_checkinimplement tci                                           ");
		sql2.append(
				" LEFT JOIN travel_nonmemgroupreserve tngr ON tngr.pkNonMemGroupReserve = tci.pkNonMemGroupReserve  ");
		sql2.append(
				" LEFT JOIN travel_nonmembergroup tng ON tng.pkNonMemberGroup = tngr.pkNonMemberGroup               ");
		sql2.append(
				" JOIN travel_roompersonalInfo trp ON trp.pkCIImplement = tci.pkCIImplement                         ");
		sql2.append(
				" JOIN travel_roomregister trr ON trr.pkRoomRegister = trp.pkRoomRegister                           ");
		sql2.append(
				" JOIN bd_room br ON br.pkRoom = trr.pkRoom                                                         ");
		sql2.append(
				" JOIN bd_personalinfo bp ON trp.pkPersonalInfo = bp.pkPersonalInfo                                 ");
		sql2.append(
				" WHERE tng.checkInDate <= :shiftChangeDate AND tng.checkOutDate >= :shiftChangeDate			                    ");
		sql2.append(
				" GROUP BY bp.sex                                                                                   ");
		List<Object[]> nList = getSession().createNativeQuery(sql2.toString())
				.setParameter("shiftChangeDate", shiftChangeDate).list();

		for (int i = 0; i < gList.size(); i++) {
			Object[] objects = gList.get(i);
			if ((objects[0] == null ? "" : objects[0].toString()).equals("Female")) {
				memberCountView.setFemaleCount(Integer.parseInt(objects[1].toString()));
			} else if ((objects[0] == null ? "" : objects[0].toString()).equals("Male")) {
				memberCountView.setMaleCount(Integer.parseInt(objects[1].toString()));
			}
		}
		for (int i = 0; i < nList.size(); i++) {
			Object[] objects = nList.get(i);
			if ((objects[0] == null ? "" : objects[0].toString()).equals("Female")) {
				int femaleCount = memberCountView.getFemaleCount() == null ? 0 : memberCountView.getFemaleCount();
				memberCountView
						.setFemaleCount(objects[1] == null ? 0 : Integer.parseInt(objects[1].toString()) + femaleCount);
			} else if ((objects[0] == null ? "" : objects[0].toString()).equals("Male")) {
				int maleCount = memberCountView.getMaleCount() == null ? 0 : memberCountView.getMaleCount();
				memberCountView
						.setMaleCount(objects[1] == null ? 0 : Integer.parseInt(objects[1].toString()) + maleCount);
			}
		}
		if (memberCountView.getFemaleCount() == null) {
			memberCountView.setFemaleCount(0);
		}
		if (memberCountView.getMaleCount() == null) {
			memberCountView.setMaleCount(0);
		}
		memberCountView.setTotalCount(memberCountView.getFemaleCount() + memberCountView.getMaleCount());

		// 80以上散客
		StringBuffer sql3 = new StringBuffer();
		sql3.append(
				" SELECT                                                                                            ");
		sql3.append(
				" 	count(*)                                                                                        ");
		sql3.append(
				" FROM                                                                                              ");
		sql3.append(
				" 	travel_checkinimplement tci                                                                     ");
		sql3.append(
				"  JOIN travel_nonmemgroupreserve tngr ON tngr.pkNonMemGroupReserve = tci.pkNonMemGroupReserve  	");
		sql3.append(
				"  JOIN travel_nonmembergroup tng ON tng.pkNonMemberGroup = tngr.pkNonMemberGroup               	");
		sql3.append(
				" JOIN travel_roompersonalInfo trp ON trp.pkCIImplement = tci.pkCIImplement                         ");
		sql3.append(
				" JOIN travel_roomregister trr ON trr.pkRoomRegister = trp.pkRoomRegister                           ");
		sql3.append(
				" JOIN bd_room br ON br.pkRoom = trr.pkRoom                                                         ");
		sql3.append(
				" JOIN bd_personalinfo bp ON trp.pkPersonalInfo = bp.pkPersonalInfo                                 ");
		sql3.append(
				" WHERE                                                                                             ");
		sql3.append(
				" 	tng.checkInDate <= :shiftChangeDate                                             			             ");
		sql3.append(
				" AND tng.checkOutDate >= :shiftChangeDate                                                       	        ");
		sql3.append(
				" AND TIMESTAMPDIFF(year, bp.birthday, NOW()) > 80                                                   ");
		Integer nonCount = Integer.parseInt(getSession().createNativeQuery(sql3.toString())
				.setParameter("shiftChangeDate", shiftChangeDate).uniqueResult().toString());

		/*
		 * List<Object[]> neList =
		 * getSession().createNativeQuery(sql3.toString()).setParameter(
		 * "startDate", startDate). setParameter("endDate", endDate). list();
		 */
		// 80以上老人团
		StringBuffer sql4 = new StringBuffer();
		sql4.append(
				" SELECT                                                                                            ");
		sql4.append(
				" 	count(*)                                                                                        ");
		sql4.append(
				" FROM                                                                                              ");
		sql4.append(
				" 	travel_checkinimplement tci                                                                     ");
		sql4.append(
				"  JOIN travel_groupreserve tgr ON tgr.pkGroupReserve = tci.pkGroupReserve   						");
		sql4.append(
				" JOIN travel_roompersonalInfo trp ON trp.pkCIImplement = tci.pkCIImplement                         ");
		sql4.append(
				" JOIN travel_roomregister trr ON trr.pkRoomRegister = trp.pkRoomRegister                           ");
		sql4.append(
				" JOIN bd_room br ON br.pkRoom = trr.pkRoom                                                         ");
		sql4.append(
				" JOIN bd_personalinfo bp ON trp.pkPersonalInfo = bp.pkPersonalInfo                                 ");
		sql4.append(
				" WHERE                                                                                             ");
		sql4.append(
				" 	tgr.checkInDate <= :shiftChangeDate                                             			             ");
		sql4.append(
				" AND tgr.checkOutDate >= :shiftChangeDate                                                       	        ");
		sql4.append(
				" AND TIMESTAMPDIFF(year, bp.birthday, NOW()) > 80                                                   ");

		Integer groupCount = Integer.parseInt(getSession().createNativeQuery(sql4.toString())
				.setParameter("shiftChangeDate", shiftChangeDate).uniqueResult().toString());

		/*
		 * List<Object[]> geList =
		 * getSession().createNativeQuery(sql4.toString()).setParameter(
		 * "startDate", startDate). setParameter("endDate", endDate). list();
		 */
		int count = 0;
		if (nonCount != null) {
			count += nonCount;
		}
		if (groupCount != null) {
			count += groupCount;
		}
		memberCountView.setEightyUpCount(count);
		List<MemberCountView> viewList = new ArrayList<>();
		viewList.add(memberCountView);
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ChargeTotalView> queryCharge(Date startDate, Date endDate) {
		StringBuffer sql = new StringBuffer();
		//团结算   支付方式，预收合计，实收合计
		sql.append(" SELECT top.paymentMethods,sum(tco.sum),sum(tci.balance) FROM travel_checkinimplement tci    ");
		sql.append("  JOIN travel_org_paymentmethods top ON tci.pkCIImplement = top.pkCIImplement ");
		sql.append("  JOIN travel_roompersonalInfo trp ON trp.pkCIImplement = tci.pkCIImplement ");
		sql.append("  JOIN travel_checkoutsettlement tco ON tco.pkCheckOutSettlement = trp.pkCheckOutSettlement  ");
		//条件：已结算   结算日期是当天
		sql.append("  WHERE tci.statementStatus = 'Statement'                                       ");
		sql.append(" and tci.statementDate <= :endDate and tci.statementDate >= :startDate          ");
		sql.append("  GROUP BY top.paymentMethods                                                   ");

		List<Object[]> list = getSession().createNativeQuery(sql.toString()).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).list();
		ChargeTotalView chargeTotalView = new ChargeTotalView();
		chargeTotalView.setCard(0);
		chargeTotalView.setCash(0);
		chargeTotalView.setTransfer(0);
		chargeTotalView.setOnCredit(0);
		for (int i = 0; i < list.size(); i++) {
			Object[] objects = list.get(i);
			if (objects[0] != null && "Cash".equals(objects[0].toString())) {
				chargeTotalView.setCash(Double.parseDouble(objects[2].toString()));
				continue;
			}
			if (objects[0] != null && "OnCredit".equals(objects[0].toString())) {
				chargeTotalView.setOnCredit(Double.parseDouble(objects[2].toString()));
				continue;
			}
			if (objects[0] != null && "WipeCard".equals(objects[0].toString())) {
				chargeTotalView.setCard(Double.parseDouble(objects[2].toString()));
				continue;
			}
			if (objects[0] != null && "Transfer".equals(objects[0].toString())) {
				chargeTotalView.setTransfer(Double.parseDouble(objects[2].toString()));
				continue;
			}
		}
		StringBuffer nonSql = new StringBuffer();
		nonSql.append(" 	 SELECT tnp.paymentMethods,sum(tcd.money) FROM travel_checkinimplement tci ");
		nonSql.append(" 	JOIN travel_checkoutdeal tcd ON tcd.pkCheckInImplement = tci.pkCIImplement ");
		nonSql.append(" 	JOIN travel_nomem_paymentmethods tnp ON tnp.pkNoMem = tcd.pkCheckOutDeal   ");
		nonSql.append(" 	  WHERE tci.statementStatus = 'Statement'                                  ");     
		nonSql.append(" 	 and tci.statementDate <= :endDate and tci.statementDate >= :startDate   ");       
		nonSql.append("  GROUP BY tnp.paymentMethods                                                   ");
		List<Object[]> nonList = getSession().createNativeQuery(nonSql.toString()).setParameter("startDate", startDate)
												.setParameter("endDate", endDate).list();
		for (int i = 0; i < nonList.size(); i++) {
			Object[] objects = nonList.get(i);
			if (objects[0] != null && "Cash".equals(objects[0].toString())) {
				chargeTotalView.setCash(Double.parseDouble(objects[1].toString()) + chargeTotalView.getCash());
				continue;
			}
			if (objects[0] != null && "OnCredit".equals(objects[0].toString())) {
				chargeTotalView.setOnCredit(Double.parseDouble(objects[1].toString()) + chargeTotalView.getOnCredit());
				continue;
			}
			if (objects[0] != null && "WipeCard".equals(objects[0].toString())) {
				chargeTotalView.setCard(Double.parseDouble(objects[1].toString()) + chargeTotalView.getCard());
				continue;
			}
			if (objects[0] != null && "Transfer".equals(objects[0].toString())) {
				chargeTotalView.setTransfer(Double.parseDouble(objects[1].toString()) + chargeTotalView.getTransfer());
				continue;
			}
		}
		chargeTotalView.setReceived(chargeTotalView.getCard() + chargeTotalView.getCash()
				+ chargeTotalView.getOnCredit() + chargeTotalView.getTransfer());
		chargeTotalView.setAdvance(chargeTotalView.getReceived());
		List<ChargeTotalView> viewList = new ArrayList<>();
		viewList.add(chargeTotalView);
		return viewList;
	}
}
